package com.example.poi.controller.download;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.meyoung.shianyun_jg.entity.Czjl.Czjl;
import com.meyoung.shianyun_jg.mapper.Czjl.CzjlMapper;
import com.meyoung.shianyun_jg.mapper.department.DepartmentMapper;
import com.meyoung.shianyun_jg.mapper.enterprise.EnterpriseMapper;
import com.meyoung.shianyun_jg.util.ModelINFO;
@Controller
@RequestMapping("request2")
public class ExportExcelController2 extends HttpServlet {
	/**
	 * @param 定义版本号
	 */
	private static final long serialVersionUID = 1L;
	@Autowired
	private  CzjlMapper czjlMapper;
	@Autowired
	private EnterpriseMapper enterpriseMapper;
	@Autowired
	private DepartmentMapper departmentMapper; 
	//解决静态方法中注入问题
	private static ExportExcelController2 excel;
	@PostConstruct  
    public void init() {  
		excel = this;  
		excel.czjlMapper =this.czjlMapper;
		excel.enterpriseMapper = this.enterpriseMapper;
		excel.departmentMapper = this.departmentMapper;
    }  
	/**
	 * @author zhujf
	 * @param request 接收客户端请求
	 * @param response 响应客户端请求
	 * @throws ServletException
	 * @throws IOException
	 * 
	 * 这种方式是在服务器上生产文件，然后读取服务器文件，并把文件流放进response响应了中返回给客户端
	 * 缺点：因为需要生成文件，所以当生成文件名相同时，有可能把B查出的数据写到A查出的数据里，此时只有把创建文件
	 * 的方法写成静态的，并加锁，此时效率很低（如果导出文件的名字都不一样，就不需要写成静态方法，让多线程同时操作）
	 * 
	 */
	@RequestMapping(value = "exportaction")
	@ResponseBody
	public void doGet(HttpServletRequest request, HttpServletResponse response,@RequestParam(value = "type", required = false)String type,@RequestParam(value = "tablename", required = false)String tablename)
			throws ServletException, IOException {
		if("czjl".equals(type)){
			String[] names = new String[]{"操作模块","操作类型","操作时间","操作IP"};
			String str = CreateFile(request,names,type,tablename,response);
			download(request,str, response,tablename+".xls");
			deleteExcel(str);
		}else if("qyda".equals(type)){
			String[] names = new String[]{"企业名称","营业执照","经营地址","法定代表人","监管单位","企业类型","经营方式","联系电话","许可证到期时间"};
			String str = CreateFile(request,names,type,tablename,response);
			download(request,str, response,tablename+".xls");
			deleteExcel(str);
		}
		
		
	}
	
	@SuppressWarnings("unchecked")
	public static synchronized String CreateFile (HttpServletRequest request,String[] names,String type,String tablename,HttpServletResponse response) throws IOException{
		
		//String[] names = new String[]{"操作模块","操作类型","操作时间","操作IP"};
		
		Object[] obj = creatwork(names);
		
		if("czjl".equals(type)){
			//获取结果集
			List<Czjl> czlist = excel.czjlMapper.queryTotal(request.getSession().getAttribute("userid").toString());
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			// 循环将数据写入Excel
			Czjl czjl = null;
			if(czlist != null){
				for (int i = 0; i < czlist.size(); i++) {
					obj[2]= ((HSSFSheet)obj[1]).createRow((int) i + 1);  
					czjl= czlist.get(i);        
					// 创建单元格，设置值       
					((HSSFRow)obj[2]).createCell(0).setCellValue(czjl.getOpModle() == null?"":czjl.getOpModle());        
					((HSSFRow)obj[2]).createCell(1).setCellValue(czjl.getOpType() == null?"":czjl.getOpType());        
					((HSSFRow)obj[2]).createCell(2).setCellValue(sdf.format(czjl.getCreateDate()));        
					((HSSFRow)obj[2]).createCell(3).setCellValue(czjl.getcIp() == null?"":czjl.getcIp());        
					}
			}
			
		}else if("qyda".equals(type)){

			//获取结果集
			Map<String,Object> map = new HashMap<String,Object>();
			Map<String,Object> companyMap = new HashMap<String,Object>();
			List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
			List<String> list =  null;
			
			String flag = (String) request.getSession().getAttribute("flag");
			if("add".equals(flag)){
				map = (Map<String, Object>) request.getSession().getAttribute("enterpriseIdlist");
				//统计某一天/月/年的新增入网企业数
				list = excel.enterpriseMapper.queryTodayAdd(map);
				//获取结果集
				for(String enterId: list){
					companyMap = excel.enterpriseMapper.queryTodayAddById(enterId);
					mapList.add(companyMap);
				}
				
			}
			if("alreadynet".equals(flag)){
				map = (Map<String, Object>) request.getSession().getAttribute("enterpriseIdlist");
				//统计截止到某一年/月/日的入网企业数
				list = excel.enterpriseMapper.queryEndYMD(map);
				//获取结果集
				for(String enterId: list){
					companyMap = excel.enterpriseMapper.queryTodayAddById(enterId);
					mapList.add(companyMap);
				}
			}
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			// 循环将数据写入Excel
			String subject_trade = "";
			String arr[] = null;
			StringBuilder builder = new StringBuilder();
			if(mapList != null){
				for (int i = 0; i < mapList.size(); i++) {
					obj[2] =((HSSFSheet)obj[1]).createRow((int) i + 1);  
					companyMap= mapList.get(i); 
					//企业类型
					
					if(companyMap.get("SUBJECT_TRADE")!=null){
						if("0".equals(companyMap.get("SUBJECT_TRADE").toString())){
							subject_trade = ModelINFO.PRODUCE;
						}else if("1".equals(companyMap.get("SUBJECT_TRADE").toString())){
							subject_trade = ModelINFO.XIAOSHOU;
						}else if("2".equals(companyMap.get("SUBJECT_TRADE").toString())){
							subject_trade = ModelINFO.CANYIN;
						}else if("3".equals(companyMap.get("SUBJECT_TRADE").toString())){
							subject_trade = ModelINFO.DANWEIST;
						}
					}
					//经营方式
					if(companyMap.get("BUSINESS_MODE")!=null){
						arr = companyMap.get("BUSINESS_MODE").toString().split(",");
						for(String str : arr){
							if("0".equals(str)){
								builder.append(ModelINFO.SC);
							}else if("1".equals(str)){
								builder.append(ModelINFO.LS);
							}else if("2".equals(str)){
								builder.append(ModelINFO.PFJLS);
							}else if("3".equals(str)){
								builder.append(ModelINFO.WLJY);
							}else if("4".equals(str)){
								builder.append(ModelINFO.ZYCF);
							}else if("4".equals(str)){
								builder.append(ModelINFO.YCPS);
							}
						}
					}
					// 创建单元格，设置值       
					((HSSFRow)obj[2]).createCell(0).setCellValue(companyMap.get("ENTERPRISE_NAME")==null?"":companyMap.get("ENTERPRISE_NAME").toString());        
					((HSSFRow)obj[2]).createCell(1).setCellValue(companyMap.get("LIENCECODE")==null?"":companyMap.get("LIENCECODE").toString());        
					((HSSFRow)obj[2]).createCell(2).setCellValue(companyMap.get("ADDRESS")==null?"":companyMap.get("ADDRESS").toString());        
					((HSSFRow)obj[2]).createCell(3).setCellValue(companyMap.get("LEGAL_PERSON")==null?"":companyMap.get("LEGAL_PERSON").toString());  
					((HSSFRow)obj[2]).createCell(4).setCellValue(companyMap.get("DEPT_CODE") == null?"":excel.departmentMapper.getDept_name((String)companyMap.get("DEPT_CODE"))); 
					((HSSFRow)obj[2]).createCell(5).setCellValue(subject_trade);   
					((HSSFRow)obj[2]).createCell(6).setCellValue(builder.toString());   
					((HSSFRow)obj[2]).createCell(7).setCellValue(companyMap.get("MOBILE")==null?"":companyMap.get("MOBILE").toString());   
					((HSSFRow)obj[2]).createCell(8).setCellValue(companyMap.get("EFFECTDATE")==null?"":companyMap.get("EFFECTDATE").toString());   
					  
					}
			}
		}
		
		
		String path =request.getSession().getServletContext().getRealPath("");
		//将数据写入excel
			FileOutputStream out =new FileOutputStream(path+tablename+".xls");
			((HSSFWorkbook)obj[0]).write(out); 
			out.close();
		((HSSFWorkbook)obj[0]).write(out); 
		
		
		return path+tablename+".xls";
	}
	
	
	private void download(HttpServletRequest request,String path, HttpServletResponse response,String name) {
		try {
			// path是指欲下载的文件的路径。
			File file = new File(path);
			// 取得文件名。
			String filename = file.getName();
			// 以流的形式下载文件。
			InputStream fis = new BufferedInputStream(new FileInputStream(path));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);
			fis.close();
			// 清空response
			response.reset();
			// 设置response的Header
			//设置不同浏览器编码（防止乱码）
		    String userAgent=request.getHeader("User-Agent");
			if (  (userAgent.indexOf("Trident")>0) && (userAgent.toUpperCase().indexOf("MSIE") > 0 || userAgent.indexOf("rv") > 0)){  
				name = URLEncoder.encode(name, "UTF-8");  
			} else {  
				name = new String(name.getBytes("UTF-8"), "ISO8859-1");  
			} 
			
			response.addHeader("Content-Disposition", "attachment;filename="+ name);
			
			response.addHeader("Content-Length", "" + file.length());
			
			OutputStream toClient = new BufferedOutputStream(
					response.getOutputStream());
			response.setContentType("application/vnd.ms-excel;charset=iso-8859-1");
			toClient.write(buffer);
			toClient.flush();
			toClient.close();
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}
	
	//提取工作表设置
	public synchronized   static Object[] creatwork(String[] names){
		
		// 1.创建一个workbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
				// 2.在workbook中添加一个sheet，对应Excel中的一个sheet
		HSSFSheet sheet = wb.createSheet("b");
				// 3.在sheet中添加表头第0行
				 HSSFRow row = sheet.createRow((int) 0);
				// 生成一个样式，用来设置标题样式
				 HSSFCellStyle style = wb.createCellStyle();
				// 居中格式
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				
				// 设置这些样式
				style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
				style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
				style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
				style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				style.setBorderRight(HSSFCellStyle.BORDER_THIN);
				style.setBorderTop(HSSFCellStyle.BORDER_THIN);
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				// 生成一个字体
				HSSFFont font = wb.createFont();
				font.setColor(HSSFColor.VIOLET.index);
				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				// 把字体应用到当前的样式
				style.setFont(font);
				
				
				// 生成并设置另一个样式,用于设置内容样式
				HSSFCellStyle style2 = wb.createCellStyle();
				style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
				style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
				style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
				style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
				style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
				style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
				// 生成另一个字体
				HSSFFont font2 = wb.createFont();
				font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
				// 把字体应用到当前的样式
				style2.setFont(font2);
				
				// 设置表格默认列宽度为15个字符
				sheet.setDefaultColumnWidth(20);
		
				
				
				HSSFCell cell =null;
				   
				
				
				
				
				for(int i = 0; i<names.length;i++){
					cell = row.createCell(i);
					cell.setCellValue(names[i]);
					cell.setCellStyle(style);
				}
				
				Object[] objarr = new Object[]{wb,sheet,row};
				
				
				
		        return objarr;
	}
	
	
	
	
	
	
	
	//中文字符转变为utf-8
	public static String toUtf8String(String s){
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < s.length(); i++) {
			char c = s.charAt(i);
			if (c >= 0 && c <= 255) {
				sb.append(c);
			} else {
				byte[] b;
				try {
					b = Character.toString(c).getBytes("utf-8");
				} catch (Exception ex) {
					ex.printStackTrace();
					b = new byte[0];
				}
				for (int j = 0; j < b.length; j++) {
					int k = b[j];
					if (k < 0)
						k += 256;
					sb.append("%" + Integer.toHexString(k).toUpperCase());
				}
			}
		}
		return sb.toString();
	}
	
	
	
	/** 
     * 删除文件. 
     * @param fileDir  文件路径 
     */  
    public boolean deleteExcel(String fileDir){  
        boolean flag = false;  
        File file = new File(fileDir);  
        // 判断目录或文件是否存在    
        if (!file.exists()) {  // 不存在返回 false    
            return flag;    
        } else {    
            // 判断是否为文件    
            if (file.isFile()) {  // 为文件时调用删除文件方法    
                file.delete();  
                flag = true;  
            }   
        }  
        return flag;  
    }  
}